﻿SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET ANSI_WARNINGS ON
GO
GO

CREATE TABLE [dbo].[bht_NemoTableTimes](
	[GroupId] [int] NOT NULL,
	[LastTime] [datetime] NOT NULL,
 CONSTRAINT [PK_bht_NemoTableTimes] PRIMARY KEY CLUSTERED 
(
	[GroupId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[bht_NemoTableTimes_TempWork](
	[GroupId] [int] NOT NULL,
	[LastTime] [datetime] NULL
) ON [PRIMARY]

GO




CREATE PROCEDURE [dbo].[bhp_NemoTablesLastModificationTimeGet]
AS
BEGIN
	DECLARE 
		@TabName	VARCHAR(200),
		@TabId		INT,
		@RelTabId	INT,
		@Type		VARCHAR(10),
		@Sql		VARCHAR(8000)
		
	SET XACT_ABORT ON
	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
	BEGIN TRANSACTION

	DELETE FROM bht_NemoTableTimes_TempWork
	
	DECLARE ntables CURSOR LOCAL FAST_FORWARD FOR
		SELECT
			t.typ,
			t.id,
			name,
			t.rel
		FROM
			n_Tree t
		WHERE
			typ = 'fn' OR typ = 't'
	
	OPEN ntables
	
	WHILE 1 = 1
	BEGIN
	
		FETCH NEXT FROM ntables INTO @Type, @TabId, @TabName, @RelTabId
		IF(@@FETCH_STATUS <> 0) BREAK;
		
		IF @Type = 't'
		
		SET @Sql = 
			'INSERT INTO bht_NemoTableTimes_TempWork
			SELECT 
				' + CAST(@TabId as NVARCHAR(100)) + ' as TabId, MAX(mod_time) as LastModTime
			FROM
				(SELECT MAX(ctime) as mod_time FROM ' + @TabName + ' WHERE ctime IS NOT NULL
				UNION
				SELECT MAX(mtime) FROM ' + @TabName + ' WHERE mtime IS NOT NULL) x
			WHERE x.mod_time IS NOT NULL'
		
		--ELSE
			--PRINT @TabName
		--SET @Sql = 
		--	'INSERT INTO bht_NemoTableTimes_TempWork
		--	SELECT
		--		' + CAST(@TabId AS VARCHAR(10)) + ', MAX(mod_time)
		--	FROM(
		--		SELECT
		--			MAX(ctime) as mod_time
		--		FROM
		--			T' + CAST(@TabId AS VARCHAR(10)) + '_T' + CAST(@RelTabId AS VARCHAR(10)) + '  WHERE ctime IS NOT NULL
		--		UNION
		--		SELECT
		--			MAX(ctime)
		--		FROM
		--			T' + CAST(@TabId AS VARCHAR(10)) + '_T' + CAST(@RelTabId AS VARCHAR(10)) + ' WHERE mtime IS NOT NULL) x
		--	WHERE x.mod_time IS NOT NULL'
		
		--PRINT @Sql
		EXEC(@Sql)
		
	END
	
	CLOSE ntables

	INSERT INTO bht_NemoTableTimes_TempWork
	SELECT 
		0, MAX([time])
	FROM
		(SELECT
			ctime as [time]
		FROM
			n_Tree
		WHERE
			ctime IS NOT NULL
		UNION
		SELECT
			mtime
		FROM
			n_Tree
		WHERE
			mtime IS NOT NULL) x

	DELETE FROM bht_NemoTableTimes

	INSERT INTO bht_NemoTableTimes
	SELECT 
		GroupId, MAX(LastTime)
	FROM 
		bht_NemoTableTimes_TempWork
	WHERE
		LastTime IS NOT NULL
	GROUP BY 
		GroupId
	
	COMMIT TRANSACTION
	
	SELECT * FROM bht_NemoTableTimes
END